500 MB Nyheder Direkte fra Internet 2

home *** CD-ROM | disk | FTP | other *** search

/ 500 MB Nyheder Direkte fra Internet 2 / 500 MB nyheder direkte fra internet CD 2.iso / start / data / text / dbase.txt < prev next >

Wrap

Text File | 1994-09-21 | 87KB | 1,870 lines

GET COMFORTABLE DBASE is a programming language that lets you easily manipulate random-access files and databases. Invented by Wayne Ratliff, was published by Ashton-Tate, which sold over a million copies. In 1991, Borland bought Ashton-Tate, so now DBASE is published by Borland. Versions of DBASE The original version of DBASE was called DBASE 2. It ran on the IBM PC and also on computers using the CP/M operating system. Then came an improvement called DBASE 3, then DBASE 3+, then DBASE 4, then DBASE 4 version 1.1, then DBASE 4 version 1.5, then DBASE 4 version 2, then DBASE 5. Those improvements run just on the IBM PC (and clones). Two versions of DBASE 5 are available: one for DOS, the other for Windows. A company called Fox Software invented DBASE versions that run faster, contain extra features, and cost less! Microsoft bought Fox Software, so now Fox's versions are published by Microsoft. Fox's versions of DBASE are called FOXBASE (which resembles DBASE 3), FOXBASE+ (which resembles DBASE 3+), FOXPRO (which resembles DBASE 4), and FOXPRO 2 (which goes beyond DBASE 4), FOXPRO 2.5 (which goes even further), and FOXPRO 2.6 (which goes even further). Two versions of FOXPRO 2.6 are available: one for DOS, the other for Windows. A Mac version of FOXPRO 2.6 isn't available yet, but you can get a Mac version of FOXPRO 2.5. Discount dealers sell each of those versions (FOXPRO 2.6 DOS, FOXPRO 2.6 Windows, and FOXPRO 2.5 Mac) for about $90, though that temporary low price will probably rise. What's in this chapter I'll explain how to use DBASE 4 version 1.5. (Newer versions of DBASE are similar.) I'll also explain the differences in DBASE 3, DBASE 3+, DBASE 4 version 1.1, and FOXPRO 2. (Newer versions of FOXPRO are similar to FOXPRO 2.) Hardware requirements Fancy versions (such as DBASE 4, FOXBASE+, FOXPRO, and FOXPRO 2) require a hard disk. If you lack a hard disk, use DBASE 3 or 3+. DBASE 4 requires 640K of RAM. FOXPRO and FOXPRO 2 require 512K. If you don't have so much RAM, use DBASE 3 or 3+, which require just 384K. To use FOXPRO 2's advanced commands, you need at least 1½M of RAM and either a 386 or 486 CPU. To use FOXPRO 2 easily, you need a mouse. Copy to the hard disk When you buy DBASE 4 version 1.5, you get a big box that contains five manuals, four 1.2M floppies, and five 720K floppies. (If your disk drive needs 360K floppies instead, you can get them by mailing Borland $15 extra.) Here's how to copy DBASE 4 version 1.5 onto your hard disk. (But if you're sharing the computer, ask your colleagues whether they did this step already!) Turn on the computer without any floppy in drive A. When you see the C prompt, put the DBASE Install Disk into drive A. Type ``a:''. You'll see an A prompt. Type ``install''. The computer will say ``DBASE 4 Installation''. Press ENTER twice. The computer will say ``Software Registration''. Type your name and press ENTER. Type your company's name (if any) and press ENTER. Type your serial number, which is on the label of the 5¼-inch DBASE Install Disk. (If you remove that disk from the drive to peek at the serial number, put that disk back in the drive when you finish peeking.) While holding down the Ctrl key, press the END key. The computer will ask, ``Do you wish to install caching?'' To keep the installation procedure simple, press N. When the computer tells you, insert the other DBASE disks, and press ENTER after each insertion. The computer will say, ``DBASE 4 can be run from any directory if your AUTOEXEC.BAT file contains the necessary information.'' To keep your AUTOEXEC.BAT file simple, press the Esc key. The computer will say, ``DBASE 4 will not run properly unless adequate file and buffer space is reserved.'' Press ENTER. The computer will make your CONFIG.SYS file says ``files=99''. The computer will say, ``The installation of DBASE 4 is complete.'' Press ENTER. Turn off the computer so you can start fresh. Old versions DBASE 3 comes on three 360K disks. The first disk is the program itself; the second disk is a spare copy of the program, in case the first disk gets damaged; the third disk contains examples and utilities. DBASE 3+ comes on seven 360K disks. Two disks contain the program itself, and the other five disks are supplementary. Instead of copying DBASE 3 or 3+ to the hard disk, try using the procedures described in the next section. DBASE 4 version 1.1 comes on ten 360K disks, which you install by using a procedure similar to DBASE version 1.5. FOXPRO 2 When you buy FOXPRO 2, you get a big box that contains nine manuals and four 1.2M disks. Here's how to copy FOXPRO 2 onto your hard disk. (I assume you have a 386 or 486, at least 1½M of RAM, and a mouse. I assume you've practiced using the mouse with other software, such as Windows or Deluxe Paint.) Turn on the computer without any floppy in drive A. When you see the C prompt, put FOXPRO Disk 1 into drive A. Type ``a:install''. The computer will say, ``Fox Software Product Installation''. Press ENTER 6 times. The computer will say, ``Enter your FOXPRO Serial Number''. Type the Serial Number (which came on a sheet of paper enclosed with the disks). The computer will say, ``Enter your FOXPRO Activation Key''. Type the hidden Activation Key (which is hidden on the SECOND sheet of paper enclosed with the disks; do NOT type the DEMONSTRATION Activation Key). The computer will say, ``Please insert Disk #2''. Insert FOXPRO Disk 2 into drive A, and press ENTER. The computer will say, ``Please insert Disk #3''. Insert FOXPRO Disk 3 into drive A, and press ENTER three times. The computer will say, ``Programs To Install''. Using the mouse, click the ``Check All'' button, then click the ``Install'' button. The computer will say, ``Please insert Disk #4''. Insert FOXPRO Disk 4 into drive A, and press ENTER. The computer will say, ``Installation Complete''. Press ENTER. Turn off the computer so you can start fresh. Start DBASE To start using DBASE 4, turn on the computer without any floppy in drive A. If you've put the DO.BAT file onto your hard disk (as I recommended in the MS-DOS chapter), type ``do dbase''. If you have not put DO.BAT onto your hard disk, you must type ``cd dbase'' and then ``dbase''. (If you're using DBASE 4 version 1.0 or 1.1, the computer will say ``This software is licensed''. Press ENTER.) The computer will say ``DBASE 4 CONTROL CENTER''. Press the Esc key. The computer will ask, ``Are you sure?'' Press the Y key. At the screen's lower left corner, you'll see a period, which is called the dot prompt. After the dot prompt, you can type any DBASE command you wish. Press the CAPS LOCK key, so any commands you type will be capitalized. Old versions Here's how to start using DBASE 3 and 3+. Without putting any DBASE floppies into the drives, turn on the computer. Wait for a DOS prompt to appear. Put DBASE System Disk 1 in drive A. Make sure you see an A prompt. (If you're using a hard disk, do that by typing ``a:'' after the C prompt.) Peculiarity: if you're using DBASE 3+ version 1.1 and nobody's ever used your System Disk 1 before, you must give it an ``ID'' by following the instructions in the ``Getting Started'' booklet that came with the disk. Regardless of which version of DBASE 3+ you're using, your next step is to type ``dbase'' after the A prompt. If the bottom of the screen says ``Press ENTER to assent to the License Agreement'', press ENTER. If the computer says ``Insert System Disk 2'', do so and press ENTER. If the bottom right corner of the screen says ``Exit ___ Esc'', press the Esc key. At the screen's lower left corner, you'll see a period, which is the dot prompt. Press the CAPS LOCK key, so any commands you type will be capitalized. If you have a hard disk, type ``SET DEFAULT TO C''. If you lack a hard disk, put a blank formatted disk in drive B and type ``SET DEFAULT TO B''. FOXPRO 2 Here's how to start using FOXPRO 2. Turn on the computer without any floppy in drive A. When you see the C prompt, type ``cd foxpro2''. When you see the FOXPRO2 prompt, type ``foxprox''. (If you don't have enough RAM to run ``foxprox'', type ``fox'' instead.) In the middle of the screen, you'll see a rectangle that has the word ``Command'' at the top of it. That rectangle is called the ``Command Window''. To move that rectangle to a different part of the screen, use the mouse: point at the word ``Command'', and drag it. To change the rectangle's size, point at the dot in the rectangle's bottom right corner, and drag it. For best results, drag until the rectangle consumes most of the top third of the screen. Press the CAPS LOCK key, so any commands you type will be capitalized. Arithmetic If you want the computer to print the answer to 5+2, say ``? 5+2'' after the dot prompt, so your screen looks like this: . ? 5+2 The computer will print: 7 Notice that DBASE, like BASIC, uses a question mark to mean print. So ``? 5+2'' means: print 5+2. In DBASE, you must use the question mark; do not type the word PRINT. DBASE, like BASIC, uses the symbols +, -, *, /, ^, E, and parentheses. Unfortunately, DBASE gives the wrong answer to -5^2. According to mathematicians, -5^2 means ``the negative of 5^2'', which is ``the negative of 25'', which is -25. DBASE mistakenly thinks that -5^2 means ``the square of -5'' and therefore prints 25. FOXPRO 2 You don't see a dot prompt. Just type: ? 5+2 Your typing will appear in the Command Window. At the end of your typing, when you press the ENTER key, the computer will print this answer at the screen's bottom: 7 Strings DBASE, like BASIC, lets you use quotation marks to create strings. So if you say ___ . ? "I LOVE YOU" the computer will print: I LOVE YOU Multiple computations You can print several computations on the same line. For example, if you say ___ . ? 2+3,2-3,2*3,2/3 the computer will print: 5 -1 6 0.67 QUIT When you finish using DBASE, do not turn off the computer's power. Turning off the power will wreck the data files you've been working on. Instead of turning off the power, say QUIT, so your screen looks like this: . QUIT That QUIT command makes the computer put the finishing touches on all your data files. Then the computer will stop using DBASE, and will say: C:\> After the ``C:\>'', you can give any DOS command, or turn off the power. CREATE A DATA FILE Let's create a data file about families in your neighborhood. Begin by saying: . CREATE FAMILIES That makes the computer create, on your disk, a data file named ``FAMILIES.DBF''. (The ``.DBF'' stands for ``Data Base File''.) Complete the chart Let's store each family's NAME, annual INCOME, and POPULATION (number of people in the family). Suppose the longest family NAME is Anagnostopoulos, the highest INCOME is 125000.00, and the largest POPULATION is 13 (because the family includes a mother, father, and 11 kids). To prepare the computer to handle such data, feed the computer this chart: ┌─────┬────────────┬────────────┬───────┬─────┬───────┐ │ Num │ Field Name │ Field Type │ Width │ Dec │ Index │ ╞═════╪════════════╪════════════╪═══════╪═════╪═══════╡ │ 1 │ NAME │ Character │ 15 │ │ N │ │ 2 │ INCOME │ Numeric │ 9 │ 2 │ N │ │ 3 │ POPULATION │ Numeric │ 2 │ 0 │ N │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ └─────┴────────────┴────────────┴───────┴─────┴───────┘ The second column says that for each family we're storing these fields: the family's NAME, INCOME, and POPULATION. The left column numbers those fields: 1, 2, and 3. The third column says that each family's NAME is a string of characters, and that each family's INCOME and POPULATION are numbers. The remaining columns say that each NAME can be up to 15 characters long (such as ``Anagnostopoulos''), each INCOME can be up to 9 symbols long (such as 125000.00) and has 2 digits after the decimal point, each POPULATION can be up to 2 digits long (such as 13) and has no decimals, and No field is indexed. As soon as you say CREATE FAMILIES (and press the ENTER key at the end of that line), the computer asks you to feed it that chart. To help you start, the computer puts this on the screen: ┌─────┬────────────┬────────────┬───────┬─────┬───────┐ │ Num │ Field Name │ Field Type │ Width │ Dec │ Index │ ╞═════╪════════════╪════════════╪═══════╪═════╪═══════╡ │ 1 │ │ Character │ │ │ N │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ └─────┴────────────┴────────────┴───────┴─────┴───────┘ Just fill in all the other entries in the chart ___ and press the ENTER key after each entry. Here are the details. . . . Begin by typing NAME (by pressing the N key, then A, then M, then E). Press the ENTER key at the end of that entry. Since the computer's already typed the next entry for you (Character), press the ENTER key again. Type the next entry (15). Press ENTER. Since the computer's already typed the next entry for you (N), press the ENTER key again. Type the next entry (INCOME). Press ENTER. Start typing the next entry (Numeric). As soon as you type the N of Numeric, the computer automatically types the ``umeric'' for you and presses the ENTER key for you. Type the next entry (9); press ENTER. Type the next entry (2); press ENTER. Accept the N, by pressing ENTER again. Type the next entry (POPULATION). Since POPULATION is so long, when you finish typing it the computer will automatically beep (to warn you not to make it even longer!) and press ENTER for you. Type the next entry (N) and the next entry (2); press ENTER. Type the final entry (0). Since you've finished typing the entire table, tap the END key while holding down the CONTROL key. Old versions DBASE 3 and 3+ omit the Num and Index columns. DBASE 3 says Char/text instead of Character. When you tap the END key wwhile holding down the CONTROL key, DBASE 3+ says ``Press ENTER to confirm''; DBASE 3 says ``Hit RETURN to confirm''; obey those versions by pressing the ENTER/RETURN key. FOXPRO 2 Feed the computer this chart: Name Type Width Dec ┌──────────────────────────────────┐ │NAME Character 15 │ │INCOME Numeric 9 2│ │POPULATION Numeric 2 0│ │ │ │ │ └──────────────────────────────────┘ Begin by typing NAME. Press the TAB key at the end of that entry. Since the computer's already typed the next entry for you (Character), press the TAB key again. Type the next entry (15). Press TAB. Type the next entry (INCOME). Press TAB. Start typing the next entry (Numeric). As soon as you type the N of Numeric, the computer automatically types the ``umeric'' for you and presses the TAB key for you. Type the next entry (9); press TAB. Type the next entry (2); press TAB. Type the next entry (POPULATION); press TAB. Type the next entry (N) and the next entry (2). The computer will automatically type the final 0. Using the mouse, click the word ``OK''. Input the data The computer will ask: Input data records now? (Y/N) Tap the Y key, which means Yes. On the screen, you'll see this: NAME INCOME . POPULATION Notice that the NAME box is wide enough to hold 15 characters, the INCOME box already includes the decimal point, and the POPULATION box is wide enough to hold a 2-digit number. Fill in the boxes. Here's how. Type the first family's name (SMITH). As you type SMITH, you'll see it appear in the first box. When you finish typing SMITH, press ENTER. Type family's income (24100.19). As you type 24100.19, you'll see it appear in the second box. When you type the decimal point, the number will automatically slide toward the right edge of the box, so that the decimal point you typed is at the same place as the decimal point that was already in the box. When you finish typing the number, the computer will automatically beep and move you to the third box. Type the family's population (4). You'll see it appear in the third box. When you press the ENTER key afterwards, the computer will record what you typed. Then the boxes will become blank again, so that you can enter a second family's data. After entering the second family's data (and pressing the ENTER key if necessary), the screen will go blank again, so you can enter a third family's data. Enter data for as many families as you wish. When you've typed the data for the last family, and you're still looking at that data on the screen, tap the END key while holding down the CONTROL key. The computer will display the dot prompt, so that you can give another DBASE command. FOXPRO 2 When you finish typing an entry (such as SMITH), you can press either the ENTER key or the TAB key. In a numeric field (such as INCOME), you don't see the decimal point until you type it. When you finish typing the first family's data, that data does NOT disappear from the screen; instead, the computer lets you type the second family's data underneath the first family's. When you finish typing the last family's data, click the close box (which is the yellow square in the window's top left corner). SEE YOUR DATA After you've created a data file, you can say: . LIST That makes the computer list your file's information onto the screen, so the screen will look like this: Record# NAME INCOME POPULATION 1 SMITH 24100.19 4 2 ANAGNOSTOPOULOS 65143.26 5 3 SANCHEZ 50000.00 13 4 JONES 9873.00 2 5 SZCZEPANKIEWICZ 125000.00 4 6 SANTINI -4130.15 4 7 WONG 15691.18 3 Notice that the Sanchez family has a high income (50000.00) but must split it among 13 people (mother, father, and 11 kids). The Jones family's population is just 2: a single mother and her baby. The Santini family's income is a negative number this year, because the family invested big money in the stock market and lost. Choose your columns You can make the computer omit some of the columns. If you say ___ . LIST NAME,POPULATION the computer will list the NAME and POPULATION columns but not the INCOME column; it will list this: Record# NAME POPULATION 1 SMITH 4 2 ANAGNOSTOPOULOS 5 3 SANCHEZ 13 4 JONES 2 5 SZCZEPANKIEWICZ 4 6 SANTINI 4 7 WONG 3 If you say ___ . LIST POPULATION,NAME the computer will list the POPULATION column before the NAME column, like this: Record# POPULATION NAME 1 4 SMITH 2 5 ANAGNOSTOPOULOS 3 13 SANCHEZ 4 2 JONES 5 4 SZCZEPANKIEWICZ 6 4 SANTINI 7 3 WONG If you say ___ . LIST OFF POPULATION,NAME the computer will turn off the ``Record#'' column; it will list just this: POPULATION NAME 4 SMITH 5 ANAGNOSTOPOULOS 13 SANCHEZ 2 JONES 4 SZCZEPANKIEWICZ 4 SANTINI 3 WONG For each family, let's compute the income per person. To do that, divide the family's income by the family's population. To display the income per person, and everything else, say ___ . LIST NAME,INCOME,POPULATION,INCOME/POPULATION The computer will list this: Record# NAME INCOME POPULATION INCOME/POPULATION 1 SMITH 24100.19 4 6025.05 2 ANAGNOSTOPOULOS 65143.26 5 13028.65 3 SANCHEZ 50000.00 13 3846.15 4 JONES 9873.00 2 4936.50 5 SZCZEPANKIEWICZ 125000.00 4 31250.00 6 SANTINI -4130.15 4 -1032.54 7 WONG 15691.18 3 5230.39 LIST FOR To list just the low-income families (earning under $10,000), say: . LIST FOR INCOME<10000 The computer will list: Record# NAME INCOME POPULATION 4 JONES 9873.00 2 6 SANTINI -4130.15 4 To list just the high-income families (earning at least $50,000), say: . LIST FOR INCOME>=50000 That means: list for INCOME greater than or equal to $50,000. When you're comparing strings, an equal sign means ``begins with''. So to list every family whose NAME begins with ``SAN'', say: . LIST FOR NAME="SAN" The computer will list all data about SANCHEZ and SANTINI. To list every family whose NAME does not begin with ``SAN'', say ___ . LIST FOR NAME<>"SAN" or, if you prefer, say: . LIST FOR NAME#"SAN" To list just the ``SAN'' families whose incomes are high, say: . LIST FOR NAME="SAN" .AND. INCOME>=50000 That makes the computer list the data for SANCHEZ (whose income is 50000) but not SANTINI (whose income is -4130.15). Notice you must put periods around the word AND. DBASE also understands the word OR, which you must surround with periods. If you want to change DBASE, so that an equal sign between strings means ``exactly equals'' instead of ``begins with'', say: . SET EXACT ON The SET EXACT ON command remains in effect until you say QUIT or SET EXACT OFF. FOXPRO 2 You can omit the periods around AND and OR. For example, you can say either ``.AND.'' or ``AND''; the computer doesn't care. LIST STRUCTURE If you say ___ . LIST STRUCTURE the computer will say: Field Field name Type Width Dec Index 1 NAME Character 15 N 2 INCOME Numeric 9 2 N 3 POPULATION Numeric 2 N ** Total ** 27 The computer will also say that the file contains 7 records, and it will say the date the file was last changed. Compute the statistics The computer can do statistics. COUNT If you say ___ . COUNT the computer will count how many records are in the file. It will say: 7 records SUM If you say ___ . SUM the computer will sum all the numbers in the file. It will say: 7 records summed INCOME POPULATION 285677.48 35 That means: the sum of all the incomes is $285,677.48, and the sum of all the populations is 35. So altogether, your entire neighborhood earns a total of $285,677.48, and the neighborhood's total population is 35. AVERAGE If you say ___ . AVERAGE the computer will average all the numbers in the file. It will say: 7 records averaged INCOME POPULATION 40811.07 5 That means the average family INCOME is $40,811.07, and the average family POPULATION is 5. (The average family population would be much lower if the Sanchez family didn't have 13 members.) Restrictions After the word COUNT, SUM, or AVERAGE, you can add restrictions. For example, if you want to find the average of just the SANCHEZ and SANTINI families, say: . AVERAGE FOR NAME="SAN" The computer will say: 2 records averaged INCOME POPULATION 22934.93 8.50 If you want to average just the incomes, and don't want to bother averaging the populations, say: . AVERAGE INCOME FOR NAME="SAN" Old versions When you say AVERAGE FOR NAME=``SAN'', the average population is exactly 8.5, but DBASE 3 and 3+ make the computer shorten the answer and say just 8. FOXPRO 2 When you say AVERAGE FOR NAME=``SAN'', the average population is exactly 8.5, but the computer rounds the answer and says 9. Print on paper To print on paper, you can use several tricks. The simplest is to tap the PRINT SCREEN key. (If your keyboard doesn't have a PRINT SCREEN key, press the PrtSc key while holding down the SHIFT key.) That makes the printer print a snapshot of what's on the screen. Another way is to type: . SET PRINT ON Afterwards, anything that will appear on the screen will also appear on paper simultaneously. The SET PRINT ON command remains in effect until you say SET PRINT OFF. To LIST on paper, you can say: . SET PRINT ON . LIST . SET PRINT OFF A faster way to LIST onto paper is to say: . LIST TO PRINT Old versions If you say LIST TO PRINT while using DBASE 3+, the last line of the listing gets temporarily lost, in a part of RAM called the ``buffer''. That last line won't get transferred to paper until afterwards, when you give your next print-to-paper command, or when you say SET PRINT ON. FOXPRO 2 If you say SET PRINT ON, anything that will appear on the screen's bottom line will appear on paper simultaneously. (Saying SET PRINT ON will NOT make the paper show what's in the Command Window.) Interrupt the computer If the computer is doing something you don't like, and you want to stop the computer, press the ESCAPE key (which says ``Esc'' on it). That makes the computer abort what it was doing. For example, suppose you say LIST, and the computer starts printing a long listing. If you get impatient and don't want to see the rest of the listing, press the ESCAPE key. In some situations, when you press the ESCAPE key, the computer asks: Cancel Ignore Suspend Confirm that you want to cancel: press the C key. After the computer aborts, it displays the dot prompt, so you can give another DBASE command. In DBASE, as in IBM's BASIC and PC-DOS, you can make the computer pause by pressing the PAUSE key. (If your keyboard doesn't have a PAUSE key, tap the NUM LOCK key while holding down the CONTROL key.) To make the computer continue where it left off, press the SPACE bar. If you say DISPLAY ALL instead of LIST, the computer will list the file but will automatically pause at the end of each screenful. At the end of each screenful, it will say, ``Press any key to continue''. When you press ENTER, the computer will continue on to the next screenful. Short-cut: instead of saying DISPLAY ALL FOR NAME=``SAN'', you can say DISPLAY FOR NAME=``SAN''. Here's the rule: before the word FOR, you can omit ALL. Old versions Instead of saying ``Cancel Ignore Suspend'', DBASE 3+ says ``Cancel, Ignore, or Suspend? (C, I, or S)''; DBASE 3 says ``Terminate command file?'', to which you respond by tapping the Y key. Grab a particular record To list just the 3rd record say: . LIST RECORD 3 The computer will say: Record# NAME INCOME POPULATION 3 SANCHEZ 50000.00 13 Instead of saying LIST RECORD 3, you can say: . GO 3 . DISPLAY That makes the computer GO to record #3 and DISPLAY it. That pair of lines (GO 3 and DISPLAY) has exactly the same effect as saying LIST RECORD 3. After you've gotten the record, you can use that record's NAME, INCOME, and POPULATION for further computations. For example, if you say ___ . ? INCOME/20 the computer will print that record's INCOME divided by 20, which is: 2500 To go to record #1, which is the top record, you can say either GO 1 or GO TOP. If your file contains 7 records, and you want to go to record #7, which is the bottom record, you can say either GO 7 or GO BOTTOM. (After giving a GO command, remember to say DISPLAY.) If you're lazy, you can usually omit the word GO. Instead of saying ___ . GO 3 you can say just: . 3 But you cannot omit the GO from ``GO TOP'' and ``GO BOTTOM''. SKIP SKIP means ``go to the next record''. For example, suppose you've been looking at record #3 (because you said GO 3 and DISPLAY), and you want to go to the next record, which is record #4. Just say: . SKIP . DISPLAY If you say SKIP 2, the computer will skip ahead 2 records. For example, if you've been looking at record #4 and then say SKIP 2, the computer will go to record #6. If you say SKIP -1, the computer will skip back to the previous record. For example, if you've been looking at record #6 and then say SKIP -1, the computer will go back to record #5. LOCATE If you say ___ . LOCATE FOR NAME="SAN" . DISPLAY the computer will start at the first record, and keep hunting until it finds a record whose NAME begins with ``SAN''. Then it will DISPLAY that record: Record# NAME INCOME POPULATION 3 SANCHEZ 50000.00 13 To find the next ``SAN'', say: . CONTINUE . DISPLAY The computer will display: Record# NAME INCOME POPULATION 6 SANTINI -4130.15 13 If you say CONTINUE again, the computer will continue hunting for SAN's. If the computer reaches the end of the file and still hasn't found another SAN, it will give up, and say: End of LOCATE scope Old versions DBASE 3 and 3+ say just ``End of LOCATE''; they omit the word ``scope''. Attach fancy restrictions If you say: . GO 3 . DISPLAY the computer will display just the 3rd record. If you say ___ . GO 3 . DISPLAY NEXT 2 the computer will display 2 records (the 3rd and the 4th). If you say ___ . GO 3 . DISPLAY NEXT 4 the computer will display 4 records (the 3rd, 4th, 5th, and 6th). If you say ___ . GO 3 . DISPLAY REST the computer will display the 3rd record and all the records that come after it. For example, if the data file contains 7 records, the computer will display the 3rd, 4th, 5th, 6th, and 7th records. (If your data file is long, the computer will pause at the end of each screenful, and wait for you to tell it to continue. If you don't want such pauses, say LIST instead of DISPLAY.) If you say ___ . GO 3 . DISPLAY WHILE INCOME<100000 the computer will start with the 3rd record, and continue displaying records as long as INCOME <100000. Here are the details. . . . The computer starts with the 3rd record, sees that its INCOME is less than 100000, and displays that record. Then the computer checks the 4th record, sees its INCOME is less than 100000, and displays that record. Then the computer checks the 5th record, sees its INCOME is not less than 100000, and refuses to display the 5th record. The computer stops there, and refuses to look at any more records. so the only records it displays are the 3rd and 4th. Those four commands ___ DISPLAY and DISPLAY NEXT and DISPLAY REST and DISPLAY WHILE ___ are all affected by where you said to GO. Three different commands ___ DISPLAY ALL and DISPLAY RECORD and DISPLAY FOR ___ are unaffected by GO. Even if you said GO 3, a DISPLAY ALL will display the entire file, DISPLAY RECORD 2 will display record #2, and DISPLAY FOR INCOME<100000 will display all the records whose INCOMEs are less than 100000. The words NEXT, REST, WHILE, ALL, RECORD, and FOR are called restrictions. You can attach a restriction to any DBASE command that scans data records. For example, you can add a restriction to DISPLAY, LIST, COUNT, SUM, AVERAGE, and LOCATE. Old versions DBASE 3 doesn't understand the word REST. REVISE YOUR DATA To revise your data, you can say EDIT, BROWSE, REPLACE, APPEND, INSERT, DELETE, or ZAP. EDIT To edit record #3, say ___ . EDIT RECORD 3 or say ___ . EDIT 3 or say: . GO 3 . EDIT The computer will display the 3rd record on the screen and let you edit it. While you edit, you can use the four arrow keys (to move around the screen), the BACKSPACE key (to erase the previous character), the DELETE key (to delete the current character), and the INSERT key (to switch from ``replacing'' to ``inserting'' and back to ``replacing'' again). DBASE handles those keys the same way as good word processors (such as Word Perfect, Q&A's word processor, and DOS 5's EDIT). To erase all the data in a field, just move to that field (by using the arrow keys); then tap the Y key while holding down the CONTROL key. After editing the 3rd record, if you press the PAGE DOWN key, the computer will let you edit the 4th record. If you press the PAGE DOWN key again, the computer will let you edit the 5th record. (Exception: if a record is too long to fit on the screen, pressing the PAGE DOWN key will get you to the next screenful of the same record.) By pressing the PAGE UP key, you can return to earlier records. For example, if you've been editing the 5th record and then press the PAGE UP key, the computer will let you re-edit the 4th record. When you've finished editing all the records you wish, tap the END key while holding down the CONTROL key. Then the computer will display the dot prompt, so you can type another DBASE command. FOXPRO 2 While you're editing the 3rd record, the screen also shows the 4th and 5th records. When you finishing editing the 3rd record, you can move to the 4th record by either pressing the PAGE DOWN key once or by pressing the down-arrow key several times. When you finish editing all the records you wish, you can either press CONTROL with END or click the close box (the yellow square in the window's top left corner). BROWSE To see several records on the screen simultaneously, so you can edit them all at once, say: . GO 1 . BROWSE That makes the computer LIST the first several records in your file. The computer will let you use the arrow keys to move through the list and edit your data. To hop right to the next field, press the TAB key; to hop left to the previous field, press the TAB key while holding down the SHIFT key. The computer will list as many records as can fit on the screen; to see the next screenful of records, press the PAGE DOWN key. The computer will list as many fields as can fit across the screen; to see other fields, tap the right-arrow key while holding down the CONTROL key. When you've finished editing the data, tap the END key while holding down the CONTROL key. Old versions In DBASE 3 and 3+, the TAB key doesn't work. Instead, hop to the next field by pressing END; hop back to the previous field by pressing HOME. FOXPRO 2 The computer will list as many fields as fit across the window. To see other fields, press the TAB key several times. The computer will list as many records as fit in the window. To see other records, press the PAGE DOWN key once or twice (or press the down-arrow key several times). When you've finished editing the data, click the close box (or press CONTROL with END). REPLACE A more literary way to edit records is to type a sentence that begins with the word REPLACE. For example, to change record #3's INCOME to $60,000, say: . GO 3 . REPLACE INCOME WITH 60000 To increase record #5's INCOME by $700, say: . GO 5 . REPLACE INCOME WITH INCOME+700 To increase everybody's INCOME by $700, say: . REPLACE ALL INCOME WITH INCOME+700 To increase the INCOME of just families whose NAME begins with ``SAN'', say: . REPLACE FOR NAME="SAN" INCOME WITH INCOME+700 APPEND To add extra records, say: . APPEND The computer will display a blank record on the screen, and let you fill it in. For example, if your data file contains 7 records, and you say APPEND, the computer will let you type record #8. When you finish typing it (and press the ENTER key at the end of the last field), the computer will let you type record #9, then record #10, etc. When you've finished typing all the records you want to add, tap the END key while holding down the CONTROL key. Then the computer will display the dot prompt, so you can type another DBASE command. FOXPRO 2 When you've finished typing all the records you want to add, click the close box (or press CONTROL with END). INSERT To insert an extra record between record #3 and record #4, say: . GO 3 . INSERT The computer will display a blank record on your screen, and let you fill it in. When you finish filling it in, tap the END key while holding down the CONTROL key. The new record that you typed will become record #4; the old record #4 will become record #5; the old record #5 will become record #6; etc. The computer will then display the dot prompt, so you can type another DBASE command. FOXPRO 2 When you finish filling in the record, click the close box (or press CONTROL with END). DELETE To delete records 3, 5, and 6, say: . DELETE RECORD 3 . DELETE RECORD 5 . DELETE RECORD 6 . PACK Here's why. When you give the DELETE commands, the computer makes notes about which records you want to delete. But it doesn't actually delete those records, until you say PACK. When you say PACK, the computer finally deletes those records. It also renumbers all the other records, to fill the gaps left by the records you deleted. Renumbering all the records takes a long time; so while the computer's doing a PACK, you should take your coffee break or lunch break. If you don't want to take a break yet, delay saying PACK until later. Say PACK at the end of the day, or when it's time to QUIT or to switch to a different file. DELETE without PACK If you say to DELETE some records, but you haven't said PACK yet, what happens when you try to LIST the file? The listing will show an asterisk next to each record you said to delete. The asterisked records will disappear later, when you say PACK. If you say ___ . SET DELETED ON the computer hides the asterisked records, so they don't appear in listings and don't affect the COUNT or SUM or AVERAGE. But although the asterisked records are hidden, they're still in the file, until you say PACK. SET DELETED ON remains in effect until you say QUIT or SET DELETED OFF. RECALL If you say DELETE RECORD 3 and then change your mind, you can get the record back by saying RECALL RECORD 3. But RECALL works only if you haven't said PACK yet. Short cuts Instead of saying DELETE RECORD 3, you can say: . GO 3 . DELETE Here's another way to delete record 3: get that record onto your screen (by saying EDIT or BROWSE, and playing with the PAGE UP and PAGE DOWN keys); and while you're looking at that record, tap the U key while holding down the CONTROL key. (The U stands for Undo.) To delete everybody whose name begins with ``SAN'', say: . DELETE FOR NAME="SAN" . PACK ZAP To delete all the records, instead of saying DELETE ALL and PACK, just say: . ZAP Then the computer asks whether you're sure; tap the Y key. Then the computer ZAPs the file, so that all the records are gone. But the computer will still remember how wide you wanted each field, so you can add new records without having to say CREATE. SWITCH FILES You can switch to a different file. Here's how. . . . USE Suppose you create a DBASE file called FAMILIES.DBF, then create a DBASE file called FOODS.DBF. If you say LIST, the computer will assume you want to list the newest file (FOODS.DBF). If you want to list FAMILIES.DBF instead, say: . USE FAMILIES . LIST Saying USE FAMILIES makes the computer switch its attention to FAMILIES.DBF, so that any future command you give (such as LIST or GO or DISPLAY) applies to FAMILIES.DBF. To switch to FOODS.DBF again, say USE FOODS. If you QUIT using DBASE but return to DBASE later, the computer forgets which file you were using. Before saying LIST, say USE FAMILIES or USE FOODS. DIR To see a directory of all the files in your hard disk's DBASE folder, say: . DIR *.* To see a special directory of just your DBASE data files (which end in .DBF), say just: . DIR That makes the computer print a special directory showing each data file's name, how many records it contains, how many bytes it contains, and the date it was last changed. COPY Suppose you're using the FAMILIES.DBF file, and want to make a backup copy of it. If you want the backup copy to be called FRED.DBF, say: . COPY TO FRED That makes FRED.DBF be an exact copy of the whole file. If you want FRED.DBF to include just the records of families whose names begin with ``SAN'', say: . COPY TO FRED FOR NAME="SAN" If you want FRED.DBF to include every family's NAME and POPULATION but not the INCOME, say: . COPY TO FRED FIELDS NAME,POPULATION If you want FRED.DBF to include no records at all, but just have the same structure (the same lengths for all the fields), say: . COPY STRUCTURE TO FRED If you want FRED.DBF to contain all the records, but rearranged so that the NAMEs are in alphabetical order, say ___ . SORT ON NAME TO FRED If you want FRED.DBF to contain all the records, but rearranged so that the INCOMEs are in increasing order, say ___ . SORT ON INCOME TO FRED If you want FRED.DBF to contain all the records, but rearranged so that the INCOMEs are in decreasing order (from the largest to the smallest), say ___ . SORT ON INCOME/D TO FRED ERASE If you ever want to erase FAMILIES.DBF, say: . ERASE FAMILIES.DBF Hassle While you're in the middle of using FAMILIES.DBF, the computer will refuse to erase it. To stop using it (so you can erase it), tell the computer to use a different file instead (by giving a command such as USE FOODS), or just say ___ .USE which makes the computer use no file at all. Then you can say ERASE FAMILIES.DBF. MODIFY STRUCTURE When you said CREATE, you filled in a chart: for each field you chose a name (such as INCOME), type (such as Numeric), and width (such as 9). If you later want to modify that chart, say: . MODIFY STRUCTURE The screen will again show the chart that you created. By using the arrow keys, you can move through the chart and modify it. You can change a field's name, type, width, or number of decimal places. To delete a field altogether, move to that field, then tap the U key while holding down the CONTROL key. (The U stands for Undo.) To insert an extra field, move to where you want the field to be, then tap the N key while holding down the CONTROL key. (The N stands for iNsert New.) Change as many fields as you like. When you've finished modifying the chart, tap the END key while holding down the CONTROL key. If the computer asks ``Should data be COPIED from backup for all fields?'', tap the Y key. The computer will ask, ``Are you sure you want to save these changes?'' Tap the Y key. The computer will revise your entire data file, so that the file matches the chart. For example, if you said to make a field wider, the computer will do so by adding extra spaces to your data; if you said to make a field narrower, the computer will do so by abridging your data. To see what the computer did to your data file, say LIST. Hassle If you make the computer change names of fields, the computer can't reliably change anything else at the same time. So to make lots of changes, say MODIFY STRUCTURE and change names of fields; afterwards say MODIFY STRUCTURE again and make all the other changes you wish (widths, decimal places, types, deleted fields, and extra fields). Old versions After giving the MODIFY STRUCTURE command, DBASE 3 and 3+ make your disk contain two versions of your data file. For example, if you've been dealing with FAMILIES.DBF, your disk will contain a file called FAMILIES.DBF (which is the new, modified version), and your disk will also contain a file called FAMILIES.BAK (which is the previous unmodified version). FAMILIES.BAK is called the backup: use it only if you accidentally made a mistake when giving the MODIFY STRUCTURE command. To use FAMILIES.BAK, say: . USE . ERASE FAMILIES.DBF . RENAME FAMILIES.BAK TO FAMILIES.DBF . USE FAMILIES FOXPRO 2 To move through the chart quickly, use the mouse: click the part of the chart that interests you. To delete a field altogether, click that field then click the word ``Delete''. To insert an extra field, click where you want the field to be then click the word ``Insert''. When you finish modifying the chart, click the word ``OK'' then click the word ``Yes''. Like DBASE 3 and 3+, FOXPRO 2 creates a .BAK file; for details, read the paragraph above (entitled ``Old versions''). INDEX FILES Suppose you're using a file called FAMILIES.DBF. Let's play a trick, so that every time you say LIST or DISPLAY the NAMEs will appear in alphabetical order, to help you find a particular NAME very quickly. Say: . INDEX ON NAME TO FAMNAME That makes the computer create a file called FAMNAME.NDX, which is an iNDeX file that helps the computer find each family's NAME. Then if you say LIST, the computer will list the families in alphabetical order: Record# NAME INCOME POPULATION 2 ANAGNOSTOPOULOS 65143.26 5 4 JONES 9873.00 2 3 SANCHEZ 50000.00 13 6 SANTINI -4130.15 4 1 SMITH 24100.19 4 5 SZCZEPANKIEWICZ 125000.00 4 7 WONG 15691.18 3 In that file, the TOP is record #2: ANAGNOSTOPOULOS. So if you say ___ . GO TOP . DISPLAY the computer will display: Record# NAME INCOME POPULATION 2 ANAGNOSTOPOULOS 65143.26 5 Then if you say ___ . SKIP . DISPLAY the computer will skip the next record in the alphabetized list, and display: Record# NAME INCOME POPULATION 4 JONES 9873.00 2 In that alphabetized file, saying GO TOP has a different effect from saying GO 1. If you say GO TOP, you're going to the TOP record, which is ANAGNOSTOPOULOS; if you say GO 1, you're going to the record #1, which is SMITH. If you want to add more records to the alphabetized list, say APPEND. As you type the extra records, the computer will automatically update the index file, so when you say LIST you'll see the entire data file ___ including even the new records ___ in alphabetical order. FOXPRO 2 When you say INDEX ON NAME TO FAMNAME, FOXPRO 2 creates an index file called FAMNAME.IDX (instead of FAMNAME.NDX). How to find To display the first record whose NAME begins with SAN, you can say: . LOCATE FOR NAME="SAN" . DISPLAY To find that record faster, say this instead ___ . SEEK "SAN" . DISPLAY or say: . FIND SAN . DISPLAY The only difference between SEEK and FIND is that SEEK must be followed by quotation marks (or a variable), whereas FIND lets you omit the quotation marks. SEEK and FIND tell the computer to find the record immediately, by using the index file. By contrast, the word LOCATE makes the computer locate the record slowly, by searching through all the records in the whole data file, from beginning to end. If your data file is very long, you'll have to wait a long time for the computer to LOCATE the record! For example, if your data file contains 2,000 records, the LOCATE command takes about 25 times as long as the SEEK and FIND commands. The main reason for creating an index file is so that you can use the words SEEK and FIND. Since the index file makes the computer list all the names in alphabetical order, all the SAN names are listed near each other. So after you've found the first SAN (by saying FIND SAN and DISPLAY), you can display the next one immediately, by saying: . SKIP . DISPLAY Hassles If you QUIT or switch to a different data file, and later try to return to FAMILIES.DBF by saying USE FAMILIES, you must remind the computer to look at the index file. Instead of saying just USE FAMILIES, say: . USE FAMILIES INDEX FAMNAME If you accidentally forget to say INDEX FAMNAME, the computer will forget to look at the index. If you then APPEND or DELETE some records, the computer won't update the index, and the index will be wrong. If you get into that jam, get out of it by saying ___ . USE FAMILIES INDEX FAMNAME . REINDEX That makes the computer create the index all over again, correctly. To see the names of all your index files (such as FAMNAME.NDX), say: . DIR *.NDX FOXPRO 2 To see the names of all your index files (such as FAMNAME.IDX), say ``DIR *.IDX''. PROGRAMS Let's write a program so that whenever you say DO STATS, the computer will automatically print the SUM and AVERAGE of all the numbers in your data file. Here's how. Begin by saying ___ . MODIFY COMMAND STATS That tells the computer you want to write a program called STATS.PRG. Next, say what you want STATS to stand for. If you want STATS to stand for ``the SUM followed by the AVERAGE'', type: SUM AVERAGE That pair of instructions (SUM and AVERAGE) is called the program. The computer does not put a dot prompt in front of the program lines. While you're typing the program, you can edit it by using the arrow keys and all the other word-processing keys (DELETE, INSERT, PAGE UP, and PAGE DOWN). To hop to the left margin, press the HOME key. To hop to the end of a line, press the END key. To delete a line, move to that line, then tap the Y key while holding down the CONTROL key. (The Y stands for ``Yank it out''.) To insert an extra line between two other lines, move to where you want the extra line to begin, then tap the N key while holding down the CONTROL key. (The N stands for ``iNsert New''.) When you've finished typing and editing the program, tap the END key while holding down the CONTROL key. That makes the computer put the entire program onto the disk. Then the screen will show a dot prompt. If you want the computer to DO the STATS program you typed, say: . DO STATS Then the computer will print the SUM and AVERAGE of all the numbers in the current data file. For example, if you've been using the FAMILIES data file, the computer will print the SUM of the INCOMEs, SUM of the POPULATIONs, AVERAGE of the INCOMEs, and AVERAGE of the POPULATIONs. (If you haven't been using a data file, the computer will ask you which file to USE. If your data file doesn't contain any Numeric fields, the computer will gripe.) If you ever want to revise that program, just say MODIFY COMMAND STATS again. The screen will show your program again and let you edit it. When you've finished editing it, tap the END key while holding down the CONTROL key. The new version of your program will be called STATS.PRG; the previous version of your program will still be on the disk but will be called STATS.BAK. For another example, let's program the computer so that when you say DO SUPERDIR, the computer will print a superdirectory. Let's make the superdirectory include a directory of all your data files, followed by a directory of all your index files, followed by a directory of all your program files. To make DO SUPERDIR accomplish all that, say: . MODIFY COMMAND SUPERDIR Then type this program ___ DIR DIR *.NDX DIR *.PRG End the program by tapping the END key while holding down the CONTROL key. From then on, whenever you say DO SUPERDIR, the computer will print a superdirectory. Into a program, you can put any DBASE commands you wish. For example, your program can include a USE command (to switch to a different data file), an APPEND command (to let the user add extra records to the file), and a BROWSE command (to let the user browse through the entire file and edit it). One of the lines in your program can even say DO a second program. When the computer encounters that line, the computer will DO the second program, then finish the original program. Your program's bottom line can even say QUIT, so that when the computer finishes the program it will stop using DBASE. Old versions In DBASE 3 and 3+, here's how to hop to the left margin: tap the left-arrow key while holding down the CONTROL key. Here's how to hop to the end of the line: tap the right arrow key while holding down the CONTROL key. FOXPRO 2 Saying ``MODIFY COMMAND STATS'' makes the computer create a window called ``STATS.PRG''. In that window, type your program, like this: SUM AVERAGE To insert an extra line, move to where you want the extra line to begin, then press ENTER. To delete a line, move to the beginning of that line, press SHIFT with down-arrow (so the line becomes brown), then press DELETE. When you've finished typing and editing the program, click the close box then click ``Yes''. In the SUPERDIR program, say ``DIR *.IDX'' instead of ``DIR *.NDX''. TEXT Let's program the computer so that whenever you say DO POEM, the computer will print this poem: YOUR DATA FILES ALL GIVE ME SMILES. I FEEL SO LOW, WHEN THEY MUST GO. PLEASE DON'T ERASE YOUR DATABASE! LOVE, YOUR COMPUTER Here's how: . MODIFY COMMAND POEM ? "YOUR DATA FILES" ? "ALL GIVE ME SMILES." ? "I FEEL SO LOW," ? "WHEN THEY MUST GO." ? "PLEASE DON'T ERASE" ? "YOUR DATABASE!" ? " LOVE," ? " YOUR COMPUTER" But typing all those question marks and quotation marks is ridiculous! For a short-cut, type this instead: . MODIFY COMMAND POEM TEXT YOUR DATA FILES ALL GIVE ME SMILES. I FEEL SO LOW, WHEN THEY MUST GO. PLEASE DON'T ERASE YOUR DATABASE! LOVE, YOUR COMPUTER ENDTEXT The words TEXT and ENDTEXT tell the computer that everything between them should be printed as strings. To begin an indented passage (such as LOVE and YOUR COMPUTER), press the TAB key. Pressing the TAB key makes the computer indent the current line and all lines underneath, until you tell the computer to stop indenting (by pressing SHIFT with TAB). Old versions In DBASE 3 and 3+, pressing the TAB key makes the computer indent just 4 spaces (instead of 8). Pressing the TAB key makes the computer indent just the current line (not the lines underneath). FOXPRO 2 Pressing the TAB key makes the computer indent just 4 spaces (instead of 8). To tell the computer to stop indenting, press the BACKSPACE key. Tricky output Here are some commands you can put in your program, to produce tricky output. To erase the screen, say: CLEAR If you say ___ ? "FAT" ?? "HER" the double question mark makes the computer print HER on the same line as FAT. The computer will print: FATHER When using DBASE, the screen's top line is called line 0. Then come lines 1, 2, etc. In each line, the leftmost character is at position 0. Then come positions 1, 2, etc. To print the word DROWN beginning at line 3's 7th position, type this: @3,7 SAY "DROWN" SET HEADING OFF When you say LIST, DISPLAY, SUM, or AVERAGE, the computer prints data in columns and puts a heading at the top of each column. If you want the computer to omit the headings, say: SET HEADING OFF That command remains in effect until you say SET HEADING ON (or QUIT). To invent your own customized heading, first get rid of the traditional headings (by saying SET HEADING OFF) and then print your own headings (by giving the ``?'' or ``@SAY'' commands). SET TALK OFF While running your program, the computer prints messages telling you which records and numbers it's manipulating. For example, if you say GO 3 and then SKIP 2, the computer prints a message saying it's skipping to record #5. If you say to COPY a file, the computer prints messages telling you how many records it's copied so far, until all the records are copied. Those messages help DBASE programmers but confuse business executives who don't understand the messages' jargon. To stop the computer from printing those messages, say: SET TALK OFF That command remains in effect until you say SET TALK ON. If you say SET TALK OFF, you'll have a hard time doing statistics: the simple COUNT, SUM, and AVERAGE commands don't work until you say SET TALK ON. Variables Like BASIC, DBASE lets you use variables easily. For example, if you say ___ X=7-1 ? X+3 the computer will print 9. (While the computer performs X=7-1, the computer will also print a little message saying that X is 6, unless you say SET TALK OFF.) A variable's name can be short (such as X) or longer. It can be up to 10 characters long (such as POPULATION). It can include underlines and digits (such as LOST_IN_86). It must begin with a letter. It cannot include blank spaces. A variable can stand for either a number or a string. For example, if you say ___ HUSBAND="TOM" ? HUSBAND the computer will print TOM. The computer handles two kinds of variables. A field variable stands for a field in a data file. For example, while you're using FAMILIES.DBF, you're using field variables called NAME, INCOME, and POPULATION. A memory variable is any variable that does not stand for a field. For example, if you say X=7-1, the X is a memory variable. Suppose you're using FAMILIES.DBF, so INCOME is a field variable. to change a family's INCOME to 20000, do not say INCOME=20000. Instead, say: REPLACE INCOME WITH 20000 Here's the rule: to change the value of a memory variable, say ``=''; to change the value of a field variable, say ``REPLACE''. The computer will remember all your variables until you say CLEAR MEMORY (which erases the memory variables) or say to USE a different data file (which affects the field variables) or say CLEAR ALL (which erases all the variables and also makes the computer forget which file you were USEing). The typical DBASE program begins by saying CLEAR ALL and ends by saying CLEAR ALL again, to make sure the variables from different programs don't interfere with each other. STORE Instead of saying X=7-1, you can say STORE 7-1 TO X. Saying ``STORE 7-1 TO X'' has exactly the same effect as saying ``X=7-1''. To make X, Y, and Z all be zero, say: STORE 0 TO X,Y,Z Statistics To make X be the SUM of all the INCOMEs in your data file, say: SUM INCOME TO X To make X be the SUM of all the INCOMEs and also make Y be the sum of all the POPULATIONs, say: SUM INCOME, POPULATION TO X,Y Similar commands work for COUNT and AVERAGE also. They all work even if you SET TALK OFF. INPUT Like BASIC, DBASE understands the word INPUT. If you say ___ INPUT "HOW OLD ARE YOU? " TO AGE the computer will ask ___ HOW OLD ARE YOU? and then wait for you to type a number. Whatever number you type will become the AGE. The INPUT statement's variable must be a memory variable, not a field variable. For example, if you're using FAMILIES.DBF and want the person to input an INCOME (which is a field variable), you must not say INPUT ``WHAT IS YOUR INCOME?'' TO INCOME. Instead, input a memory variable called MINCOME, then copy it to a field by saying REPLACE, like this: INPUT "WHAT IS YOUR INCOME? " TO MINCOME REPLACE INCOME WITH MINCOME ACCEPT To input a string instead of a number, say ACCEPT instead of INPUT: ACCEPT "WHAT IS YOUR FAVORITE FOOD? " TO FOOD Underneath that ACCEPT statement, you might want to add this line: FOOD=UPPER(FOOD) It makes the computer convert the human's input to capital (upper-case) letters, in case the human forgot to press the CAPS LOCK key. Like the INPUT statement, the ACCEPT statement takes a memory variable but not a field variable. WAIT This example says WAIT instead of ACCEPT: WAIT "WHAT'S YOUR MIDDLE INITIAL? " TO INITIAL ? "CONGRATULATIONS!" ? "ACCORDING TO ASTROLOGY, THE FORCES OF THE UNIVERSE" ? "SHALL CAUSE WONDROUS JOYS TO BEFALL" ? "THOSE LUCKY PERSONS WHOSE EARTH-GIVEN NAMES ARE CENTERED" ? "AROUND THE LETTER",INITIAL WAIT resembles ACCEPT. In that program, the first line makes the computer ask ``WHAT'S YOUR MIDDLE INITIAL?'' then wait for you to type a character. Whatever character you type will become the INITIAL. For example, if you type a Q, the Q will become the INITIAL. If the line had said ACCEPT, the computer would have required you to press the ENTER key after the Q. But since the line said WAIT instead of ACCEPT, you do not have to press ENTER: as soon as you type the Q, the computer will know Q is the INITIAL (without waiting for ENTER) and will make the screen show this: WHAT'S YOUR MIDDLE INITIAL? Q CONGRATULATIONS! ACCORDING TO ASTROLOGY, THE FORCES OF THE UNIVERSE SHALL CAUSE WONDROUS JOYS TO BEFALL THOSE LUCKY PERSONS WHOSE EARTH-GIVEN NAMES ARE CENTERED AROUND THE LETTER Q WAIT is nicer than ACCEPT, because WAIT doesn't force you to press the ENTER key after answering the question. But alas, WAIT restricts you to typing just one character: you can use WAIT for a middle initial, but not for a whole name. GET Suppose you live in San Francisco with most of your friends, but a few of your friends live elsewhere. Run this program: CLEAR ALL CLEAR SET TALK OFF CITY="SAN FRANCISCO" STATE="CA" @10,25 SAY "WHAT IS YOUR CITY?" GET CITY @11,25 SAY "WHAT IS YOUR STATE?" GET STATE READ ? "I AM GLAD TO HEAR YOU LIVE IN THE KOOKY CITY OF",CITY ? "IN THE SEDUCTIVE STATE OF",STATE SET TALK ON CLEAR ALL Like most DBASE programs, that program begins by saying CLEAR ALL (to avoid interference from other programs), CLEAR (to erase the screen), and SET TALK OFF (to avoid excessive messages). The next pair of lines make CITY be ``SAN FRANCISCO'' and make STATE be ``CA''. The next pair of lines put this message in the middle of your screen: WHAT IS YOUR CITY? SAN FRANCISCO WHAT IS YOUR STATE? CA The READ statement lets you edit the data in the boxes, by using the arrow keys, ENTER key, and other word-processing keys. (If you want to erase an entire box, move to that box, then tap the Y key while holding down the CONTROL key.) You can edit the boxes however you like ___ for example, change SAN FRANCISCO to RENO, and change CA to NV ___ or do no editing at all, so that you still have SAN FRANCISCO and CA. When you've finished doing all the editing you wish, tap the END key while holding down the CONTROL key, or just move to the bottom box and tap the ENTER key. If you changed SAN FRANCISCO to RENO and changed CA to NV, the next two lines in the program will print this, at the bottom of the screen: I AM GLAD TO HEAR YOU LIVE IN THE KOOKY CITY OF RENO IN THE SEDUCTIVE STATE OF NV Like most DBASE programs, that program ends by putting the computer back to its normal state (SET TALK ON and CLEAR ALL). To use the word GET, you should put it in an @ SAY command. Underneath the @ SAY GET commands, say READ. If you forget to include the SAY ``WHAT IS YOUR CITY?'', and instead type just @10,25 GET CITY, the computer will omit the question but will still show the CITY box. If you forget to say READ, you'll still see boxes full of data, but you won't get a chance to edit what's in them. By using GET and READ, you can edit any kind of variable. The variable can be a number or a string; it can be a memory variable or a field variable. If it's a field variable, the computer automatically makes the box wide enough to hold the entire field; and when you edit the data in the box, the computer automatically updates your data file. (You do not have to say REPLACE.) If it's a memory variable instead, your program must include a statement such as CITY=``SAN FRANCISCO'', which tells the computer how wide to make the box. To make the box even wider, put extra spaces after SAN FRANCISCO, like this: CITY="SAN FRANCISCO " If the memory variable stands for a number instead of a string, the computer automatically makes the box wide enough to hold 10 digits before the decimal point ___ or even wider. CONTROL THE FLOW You can control the order in which the computer does your program's statements. Here's how. . . . IF Like BASIC and PASCAL, DBASE lets you use the words IF and ELSE. Write the IF statement like this: IF AGE<18 ? "MINOR" ? "YOUNG" ELSE ? "ADULT" ? "OLD" ENDIF Whenever you say IF, you must also say ENDIF. To indent the lines in between, tap the TAB key. (The indentation is optional.) Here's how to say, ``If the NAME begins with SAN and the INCOME is at least 50000'': IF NAME="SAN" .AND. INCOME>=50000 Remember to put periods around the word AND. For strings, the symbol ``='' means ``begins with'', unless you said SET EXACT ON. DO WHILE To create a loop, say DO WHILE. If you want the loop to be infinite, say DO WHILE .T., like this: DO WHILE .T. ? "CAT" ? "DOG" ENDDO The computer will print CAT and DOG repeatedly, like this: CAT DOG CAT DOG CAT DOG etc. To abort the program, tap the Esc key (which stands for ``Escape'') and then the C key (which stands for ``Cancel''). Any program that says DO WHILE must also say ENDDO. The computer will repeat all the lines between DO WHILE and ENDDO, to form a loop. This program makes the computer print all the numbers from 1 to 10 and then print ``WOW'': SET TALK OFF X=1 DO WHILE X<=10 ? X X=X+1 ENDDO ? "WOW" SET TALK ON The top line prevents the computer from printing excessive messages. The next line says X starts at 1. The lines between DO WHILE and ENDDO are done repeatedly, as long as X<=10. If X gets past 10, the computer refuses to do the loop again, and instead proceeds to the line underneath ENDDO, which makes the computer say WOW. The bottom of the program says SET TALK ON, to put the computer back to normal. Altogether, the computer prints: 1 2 3 4 5 6 7 8 9 10 WOW In BASIC, you can create loops by giving commands such as ``GO TO 10'' and ``FOR I = 1 TO 10...NEXT''. Unfortunately, DBASE doesn't understand how to GO TO line 10 or how to do a FOR...NEXT loop. In DBASE, the only way to create a loop is to say DO WHILE...ENDDO. In the middle of a WHILE loop, you can put a line saying LOOP or EXIT. If the computer encounters a line saying LOOP, the computer loops back (by jumping back to the WHILE loop's top line). If the computer encounters a line saying EXIT, the computer exits from the loop (by jumping ahead to the line underneath ENDDO). RETURN Instead of using the BASIC word END, DBASE uses the word RETURN. While the computer is DOing your program, if the computer encounters a line that says RETURN, the computer will skip the rest of the program. The ULTIMATE program Congratulations! Now you know enough about DBASE so that you're ready to create the ULTIMATE program. This program lets you store and retrieve any information about any topic! Moreover, the program is so nicely designed that even a novice who knows nothing about DBASE can run the program and use its full power. How the program acts After you've written the program, you can start using it by just typing: . DO ULTIMATE Then the computer starts running the ULTIMATE program, which makes the computer ask: What topic interests you? (If unsure, type a question mark. To end, type an x.) If you type just an x or a capital X, the computer stops running the program and displays the dot prompt. If you type a question mark instead, the computer displays a list of all the topics it was fed previously. For example, those topics might include SHRUB, BUSH, QUAIL, BIRDBRAIN, MANHATTAN, SEX, THE MEANING OF LIFE, and STRANGE JOKES. Then the computer says: Pick one of those topics, or teach me a new one. What topic interests you? (If unsure, type a question mark. To end, type an x.) If you type anything other than an x or an X or a question mark, the computer searches through its file, to see whether it's been fed that topic. If it finds the topic in the file, it reveals all it knows about the topic, and then lets you edit that data. If it does not find the topic in the file, it says so, and gives you an opportunity to teach it about the topic. How to invent the program To make the computer do all that, so a novice can store and retrieve data easily, a professional (such as yourself!) must previously put three things onto the disk: the program itself (which is called ULTIMATE.PRG), a data file (called INFO.DBF), and an index file (called INFOTOP.NDX). Here's how. . . . Data file Start by putting the data file INFO.DBF onto the disk. To do that, say: . CREATE INFO Then complete this chart: ┌─────┬────────────┬────────────┬───────┬─────┬───────┐ │ Num │ Field Name │ Field Type │ Width │ Dec │ Index │ ╞═════╪════════════╪════════════╪═══════╪═════╪═══════╡ │ 1 │ TOPIC │ Character │ 25 │ │ N │ │ 2 │ DATA │ Character │ 79 │ │ N │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ └─────┴────────────┴────────────┴───────┴─────┴───────┘ That allows each TOPIC to be 25 characters long, and the DATA about the topic to be 79 characters long. After typing the chart, tap the END key while holding down the CONTROL key. Then tap the ENTER key. The computer will ask, ``Input data records now?'' Tap the N key. Index file Next, put the index file INFOTOP.NDX onto the disk, by typing: . INDEX ON TOPIC TO INFOTOP That creates an index file called INFOTOP.NDX, which lets the computer find each TOPIC quickly. The program itself Finally, put the program ULTIMATE.PRG onto the disk. To do that, begin by saying: . MODIFY COMMAND ULTIMATE Then type the program's introduction: CLEAR ALL CLEAR SET TALK OFF SET HEADING OFF SET EXACT ON SET DELETED ON USE INFO INDEX INFOTOP That introduction says to CLEAR ALL influences from previous programs, CLEAR the screen, SET the DBASE program so you have complete control over everything, and USE the INFO data file INDEXed by INFOTOP. Then type the program's loop: DO WHILE .T. ? ?"What topic interests you? " ?? "(If unsure, type a question mark. To end, type an x.)" ACCEPT TO DESIRE DESIRE=UPPER(DESIRE) CLEAR DO CASE CASE DESIRE="X" EXIT CASE DESIRE="?" GO TOP IF EOF() ? "I don't know any topics yet. My mind is blank. " ?? "Please teach me a new topic." ELSE ? "I know about these topics:" DISPLAY ALL OFF TOPIC ? "Pick one of those topics, or teach me a new one." ENDIF CASE DESIRE="" OTHERWISE SEEK DESIRE IF FOUND() ? "Here's what I know about",DESIRE+":" @2,0 GET DATA ? "You can edit that info now." ? "(If you want me to forget about",DESIRE ?? ", tap Y while holding down the CTRL key.)" ? "When you're done, tap the ENTER key." READ CLEAR IF DATA="" DELETE ENDIF ELSE ? "I don't know anything about",DESIRE+"." ? "Tell me about",DESIRE+"." ? "(If you don't want to tell me, type an x.)" ACCEPT TO NEWDATA IF UPPER(NEWDATA)#"X" APPEND BLANK REPLACE TOPIC WITH DESIRE REPLACE DATA WITH NEWDATA ENDIF ENDIF ENDCASE ENDDO In that loop, each print statement uses small letters instead of capitals, so that the printing will look more sophisticated. (To create the small letters, just turn off the CAPS LOCK key, by tapping it.) After saying ACCEPT TO DESIRE, the next statement says DESIRE=UPPER(DESIRE), which converts the user's desired topic to capital letters. That saves the user from worrying about whether to type REAGAN or Reagan or reagan: whichever of those the user types, the computer will convert it to REAGAN. The DESIRE can be four kinds of things: it can be an X (which means the user wants to exit), a question mark (which means the user is confused and would like to see a list of topics), nothing at all (which means the user accidentally pressed the ENTER key an extra time), or a topic. The DO CASE statement tells the computer to handle those four cases: DESIRE=``X'', DESIRE=``'', and DESIRE=otherwise. Let's look at those four cases in more detail. In the CASE where DESIRE=``X'', the computer lets the user EXIT from the DO WHILE loop. In the CASE where DESIRE=``?'', the computer begins by checking whether the file contains any undeleted topics. Here's how. The lines GO TOP and IF EOF() tell the computer to GO to the TOP of the file and see if the TOP of the file is also the End Of the File. If the TOP of the file is also the End Of the File, the file doesn't contain any records yet (or all its records have been DELETED), so the computer will print ``I don't know any topics yet. My mind is blank.'' If the computer's mind is not blank, the computer will say ``I know about these topics'' and will DISPLAY ALL of the TOPICS. The computer will display the topics themselves but will not display their record numbers, since the DISPLAY statement says to turn the record numbers OFF. In the CASE where DESIRE=``'', the computer does nothing at all. In the case where desire is OTHERWISE, the computer will hunt through all the topics in the file, to SEEK the user's DESIRE. If the user's DESIRE is FOUND in the file, the computer is commanded to print ``Here's what I know about'',DESIRE+``:''. In that command, the word DESIRE is followed by a plus sign instead of a comma, to prevent the computer from leaving a blank space after DESIRE. If the user's DESIRE is not FOUND in the file, the computer is commanded to print ``I don't know anything about'',DESIRE+``.''. Then the computer asks the user to type some NEWDATA about the topic. If the user does indeed type some useful NEWDATA (instead of just an X), the computer appends the NEWDATA to the end of the file. To do that, the computer first APPENDs a BLANK record, then puts the NEWDATA into that record by using REPLACE. The remaining lines say ENDIF twice (to end the two IF statements) and ENDCASE (to end the DO CASE statement) and ENDDO (to end the DO WHILE statement). At the bottom of the program, add these lines: SET DELETED OFF COUNT FOR DELETED() TO DELCOUNT IF DELCOUNT>RECCOUNT()/4 ? "Please wait, " ?? "while I compress your data." PACK ? "The compression is done." ENDIF SET EXACT OFF SET HEADING ON SET TALK ON CLEAR ALL The computer reaches those lines when the user chooses to EXIT from the DO WHILE loop (by saying the DESIRE is ``X''). Those lines say to SET the computer back to normal and to CLEAR ALL interference from later programs. One of those lines says to PACK the file. But if fewer than a quarter of the records have been marked for deletion, the program tells the computer not to bother PACKing. Here's how the computer figures out whether to bother PACKing. The program says to COUNT how many records have been marked to be DELETED, and call that the DELCOUNT. The program says that IF the DELCOUNT is greater than the RECord COUNT divided by 4, then PACK. After you've typed that entire program, tap the END key while holding down the CONTROL key. Then you're done: you've created the ULTIMATE data-management program! Consequences Some parts of that program were hard to invent, but the program is super-easy to use. Try it! Then challenge yourself: think of further improvements to the ULTIMATE program, to make the program even easier to use and even more powerful! Such programs would be much harder to develop, if we were using BASIC, LOGO, PASCAL, C, COBOL or any other major computer language. That's why programmers love DBASE! Old versions DBASE 3 doesn't understand RECCOUNT(); instead of saying IF DELCOUNT>RECCOUNT()/4, say COUNT TO RECCOUNT and then IF DELCOUNT>RECCOUNT/4. DBASE 3 doesn't understand the word FOUND. DBASE 3+ version 1.0 handles FOUND incorrectly if you deleted some records. For those versions of DBASE, instead of saying IF FOUND(), say IF .NOT. EOF().